package cn.com.zsc.hr;

import java.io.File;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Vector;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.zsc.util.ConstValue;
import cn.com.zsc.util.StringUtils;

import com.jarp.db.DAO;

/**
 * 职工管理类
 * @author 杨景
 *
 */
public class EmployeeManage {
	
	/**
	 * 获取对应条数的职工信息
	 * @param start
	 * @param limit
	 * @param isFormal
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmployee(String start, String limit, String isFormal) {
		String sql = "SELECT" +
							" B.CL_NAME AS SHOW_SEX," +
							" C.CL_NAME AS SHOW_EPMODE," +
							" D.CL_NAME AS SHOW_STARLEVEL," +
							" E.CL_NAME AS SHOW_PVISAGE," +
							" F.CL_NAME AS SHOW_DEPT," +
							" G.CL_NAME AS SHOW_CONTRACT," +
							" H.CL_NAME AS SHOW_EDUCATION," + 
							" I.CL_NAME AS SHOW_CCARD," +
							" J.CL_NAME AS SHOW_ELEVEL," +
							" K.CL_NAME AS SHOW_MLEVEL," +
							" L.CL_NAME AS SHOW_TERM," + 
							" M.CL_NAME AS SHOW_POST, " +
							" N.CL_NAME AS SHOW_DUTIES, " +
							" O.CL_NAME AS SHOW_LEFTJOB, " +
							" P.CL_NAME AS SHOW_INSURANCE, " +
							" A.* " +
					" FROM HR_EMPLOYEE A " +
							" LEFT JOIN CM_COMMLIST B ON A.SEX=B.CL_CODE " +
							" LEFT JOIN CM_COMMLIST C ON A.EPMODE=C.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST D ON A.STARLEVEL=D.CL_CODE " +
							" LEFT JOIN CM_COMMLIST E ON A.PVISAGE=E.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST F ON A.DEPT=F.CL_CODE " +
							" LEFT JOIN CM_COMMLIST G ON A.CONTRACT=G.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST H ON A.EDUCATION=H.CL_CODE " +
							" LEFT JOIN CM_COMMLIST I ON A.CCARD=I.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST J ON A.ELEVEL=J.CL_CODE " +
							" LEFT JOIN CM_COMMLIST K ON A.MLEVEL=K.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST L ON A.TERM=L.CL_CODE " +
							" LEFT JOIN CM_COMMLIST M ON A.POST=M.CL_CODE " +
							" LEFT JOIN CM_COMMLIST N ON A.DUTIES=N.CL_CODE " +
							" LEFT JOIN CM_COMMLIST O ON A.LEFTJOB=O.CL_CODE " +
							" LEFT JOIN CM_COMMLIST P ON A.INSURANCE=P.CL_CODE " +
					"where A.ISFORMAL='" + isFormal + "' order by EID desc limit " + start + "," + limit;

		DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_PAGEING;

		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				for (int i=0; i<v.size(); i++) {
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("BIRTHDAY"))) {
						v.get(i).put("BIRTHDAY", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("ARRDATE"))) {
						v.get(i).put("ARRDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("SIGNDATE"))) {
						v.get(i).put("SIGNDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("LIMITDATE"))) {
						v.get(i).put("LIMITDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("INDATE"))) {
						v.get(i).put("INDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("GRADUATION"))) {
						v.get(i).put("GRADUATION", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("AGREEMENT"))) {
						v.get(i).put("AGREEMENT", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("ENDDATE"))) {
						v.get(i).put("ENDDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("TEACHER"))) {
						v.get(i).put("TEACHER", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("INSURANCE"))) {
						v.get(i).put("INSURANCE", "");
					}
				}
				JSONArray json = JSONArray.fromObject(v);
				
				String employeeStr = json.toString();

				sql = "select count(EID) from hr_employee where ISFORMAL="+ isFormal;
				String total = dao.getDataValue(ConstValue.DBNAME, sql);
				
				resultStr = "{\"totalCount\":\"" + total + "\",\"results\":" + employeeStr + "}";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultStr;
	}
	
	/**
	 * 新增职工信息
	 * @param jso
	 * @return
	 */
	public String insertEmployee(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
	
		String workID = jsobject.getString("workID");
		String eName = jsobject.getString("eName");
		Integer sex = "".equals(jsobject.getString("sex"))?null:getClCodeByClName(jsobject.getString("sex"));
		Integer dept = "".equals(jsobject.getString("dept"))?null:getClCodeByClName(jsobject.getString("dept"));
		Integer post = "".equals(jsobject.getString("post"))?null:getClCodeByClName(jsobject.getString("post"));
		Integer duties = "".equals(jsobject.getString("duties"))?null:getClCodeByClName(jsobject.getString("duties"));
		Integer term = "".equals(jsobject.getString("term"))?null:getClCodeByClName(jsobject.getString("term"));
		String birthday = "".equals(jsobject.getString("birthday"))?ConstValue.DEFAULT_DATE:jsobject.getString("birthday");
		String age = jsobject.getString("age");
		Integer height = "".equals(jsobject.getString("height"))?null:Integer.parseInt(jsobject.getString("height"));
		String sino = jsobject.getString("sino");
		String icno = jsobject.getString("icno");
		String rprAdd = jsobject.getString("rprAdd");
		String liveAdd = jsobject.getString("liveAdd");
		String tel = jsobject.getString("tel");
		Integer contract = "".equals(jsobject.getString("contract"))?null:getClCodeByClName(jsobject.getString("contract"));
		Integer epMode = "".equals(jsobject.getString("epMode"))?null:getClCodeByClName(jsobject.getString("epMode"));
		String arrDate = "".equals(jsobject.getString("arrDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("arrDate");
		String signDate = "".equals(jsobject.getString("signDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("signDate");
		String limitDate = "".equals(jsobject.getString("limitDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("limitDate");
		String fRelation = jsobject.getString("fRelation");
		String fName = jsobject.getString("fName");
		String fAge = jsobject.getString("fAge");
		String fUnits = jsobject.getString("fUnits");
		Integer starLevel = "".equals(jsobject.getString("starLevel"))?null:getClCodeByClName(jsobject.getString("starLevel"));
		Integer pVisage = "".equals(jsobject.getString("pVisage"))?null:getClCodeByClName(jsobject.getString("pVisage"));
		String tPost = jsobject.getString("tPost");
		String gsch = jsobject.getString("gsch");
		Integer education = "".equals(jsobject.getString("education"))?null:getClCodeByClName(jsobject.getString("education"));
		Integer cCard = "".equals(jsobject.getString("cCard"))?null:getClCodeByClName(jsobject.getString("cCard"));
		Integer eLevel = "".equals(jsobject.getString("eLevel"))?null:getClCodeByClName(jsobject.getString("eLevel"));
		Integer mLevel = "".equals(jsobject.getString("mLevel"))?null:getClCodeByClName(jsobject.getString("mLevel"));
		String specialty = jsobject.getString("specialty");
		String rp = jsobject.getString("rp");
		String remarked = jsobject.getString("remarked");
		Integer isFormal = "".equals(jsobject.getString("isFormal"))?null:Integer.parseInt(jsobject.getString("isFormal"));
		Integer workAge = "".equals(jsobject.getString("workAge"))?null:Integer.parseInt(jsobject.getString("workAge"));
		Integer leftJob = "".equals(jsobject.getString("leftJob"))?null:getClCodeByClName(jsobject.getString("leftJob"));
	
		String photo = jsobject.getString("photo");
		photo = StringUtils.replace(photo, "\\", "$");
		
		String special = jsobject.getString("special");
		String inDate = "".equals(jsobject.getString("inDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("inDate");
		String graduation = "".equals(jsobject.getString("graduation"))?ConstValue.DEFAULT_DATE:jsobject.getString("graduation");
		String agreement = "".equals(jsobject.getString("agreement"))?ConstValue.DEFAULT_DATE:jsobject.getString("agreement");
		String endDate = "".equals(jsobject.getString("endDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("endDate");
		String teacher = jsobject.getString("teacher");
		Integer insurance = "".equals(jsobject.getString("insurance"))?null:getClCodeByClName(jsobject.getString("insurance"));
		
		String sql = "insert into hr_employee values(default,'" + workID
				+ "','" + eName + "'," + sex + ",'" + age + "','" + birthday
				+ "'," + height + ",'" + sino + "','" + icno + "','" + rprAdd
				+ "','" + liveAdd + "','" + tel + "'," + epMode + ",'"
				+ arrDate + "','" + signDate + "','" + limitDate + "',"
				+ workAge + "," + starLevel + "," + pVisage + ",'" + tPost
				+ "'," + dept + "," + post + "," + duties + "," + contract
				+ ",'" + gsch + "'," + education + "," + cCard + "," + eLevel
				+ "," + mLevel + ",'" + rp + "'," + term + ",'" + specialty
				+ "','" + fName + "','" + fAge + "','" + fRelation + "','"
				+ fUnits + "'," + leftJob + "," + isFormal + ",'" + photo
				+ "','" + special + "','" + inDate + "','" + graduation + "','"
				+ agreement + "','" + endDate + "','" + teacher + "',"
				+ insurance + ",'" + remarked + "')";
		
		DAO dao = new DAO();
		
		try {
			int num = dao.execCommand(ConstValue.DBNAME, sql);
			
			if (num > 0) {
				return "success";
			} else {
				return "failure";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 更新职工信息
	 * @param jso
	 * @return
	 */
	public String updateEmployee(String jso) {
        JSONObject jsobject = JSONObject.fromObject(jso);
        
        int eid = Integer.parseInt(jsobject.getString("eid"));
		String workID = jsobject.getString("workID");
		String eName = jsobject.getString("eName");
		Integer sex = "".equals(jsobject.getString("sex"))?null:getClCodeByClName(jsobject.getString("sex"));
		Integer dept = "".equals(jsobject.getString("dept"))?null:getClCodeByClName(jsobject.getString("dept"));
		Integer post = "".equals(jsobject.getString("post"))?null:getClCodeByClName(jsobject.getString("post"));
		Integer duties = "".equals(jsobject.getString("duties"))?null:getClCodeByClName(jsobject.getString("duties"));
		Integer term = "".equals(jsobject.getString("term"))?null:getClCodeByClName(jsobject.getString("term"));
		String birthday = "".equals(jsobject.getString("birthday"))?ConstValue.DEFAULT_DATE:jsobject.getString("birthday");
		String age = jsobject.getString("age");
		Integer height = "".equals(jsobject.getString("height"))?null:Integer.parseInt(jsobject.getString("height"));
		String sino = jsobject.getString("sino");
		String icno = jsobject.getString("icno");
		String rprAdd = jsobject.getString("rprAdd");
		String liveAdd = jsobject.getString("liveAdd");
		String tel = jsobject.getString("tel");
		Integer contract = "".equals(jsobject.getString("contract"))?null:getClCodeByClName(jsobject.getString("contract"));
		Integer epMode = "".equals(jsobject.getString("epMode"))?null:getClCodeByClName(jsobject.getString("epMode"));
		String arrDate = "".equals(jsobject.getString("arrDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("arrDate");
		String signDate = "".equals(jsobject.getString("signDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("signDate");
		String limitDate = "".equals(jsobject.getString("limitDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("limitDate");
		String fRelation = jsobject.getString("fRelation");
		String fName = jsobject.getString("fName");
		String fAge = jsobject.getString("fAge");
		String fUnits = jsobject.getString("fUnits");
		Integer starLevel = "".equals(jsobject.getString("starLevel"))?null:getClCodeByClName(jsobject.getString("starLevel"));
		Integer pVisage = "".equals(jsobject.getString("pVisage"))?null:getClCodeByClName(jsobject.getString("pVisage"));
		String tPost = jsobject.getString("tPost");
		String gsch = jsobject.getString("gsch");
		Integer education = "".equals(jsobject.getString("education"))?null:getClCodeByClName(jsobject.getString("education"));
		Integer cCard = "".equals(jsobject.getString("cCard"))?null:getClCodeByClName(jsobject.getString("cCard"));
		Integer eLevel = "".equals(jsobject.getString("eLevel"))?null:getClCodeByClName(jsobject.getString("eLevel"));
		Integer mLevel = "".equals(jsobject.getString("mLevel"))?null:getClCodeByClName(jsobject.getString("mLevel"));
		String specialty = jsobject.getString("specialty");
		String rp = jsobject.getString("rp");
		String remarked = jsobject.getString("remarked");
		Integer isFormal = "".equals(jsobject.getString("isFormal"))?null:Integer.parseInt(jsobject.getString("isFormal"));
		Integer workAge = "".equals(jsobject.getString("workAge"))?null:Integer.parseInt(jsobject.getString("workAge"));
		Integer leftJob = "".equals(jsobject.getString("leftJob"))?null:getClCodeByClName(jsobject.getString("leftJob"));
		
		String special = jsobject.getString("special");
		String inDate = "".equals(jsobject.getString("inDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("inDate");
		String graduation = "".equals(jsobject.getString("graduation"))?ConstValue.DEFAULT_DATE:jsobject.getString("graduation");
		String agreement = "".equals(jsobject.getString("agreement"))?ConstValue.DEFAULT_DATE:jsobject.getString("agreement");
		String endDate = "".equals(jsobject.getString("endDate"))?ConstValue.DEFAULT_DATE:jsobject.getString("endDate");
		String teacher = jsobject.getString("teacher");
		Integer insurance = "".equals(jsobject.getString("insurance"))?null:getClCodeByClName(jsobject.getString("insurance"));
		
		String photo = jsobject.getString("photo");
		if(!"".equals(photo)) {
			// 删除原来的头像
	        deleteOldImage(eid);
	        photo = StringUtils.replace(photo, "\\", "$");
		}
		
		String sql = "update hr_employee set WORKID='" + workID + "',ENAME='"
				+ eName + "',SEX=" + sex + ",AGE='" + age + "',BIRTHDAY='"
				+ birthday + "',HEIGHT=" + height + ",SINO='" + sino
				+ "',ICNO='" + icno + "',RPRADD='" + rprAdd + "',LIVEADD='"
				+ liveAdd + "',TEL='" + tel + "',EPMODE=" + epMode
				+ ",ARRDATE='" + arrDate + "',SIGNDATE='" + signDate
				+ "',LIMITDATE='" + limitDate + "',WORKAGE=" + workAge
				+ ",STARLEVEL=" + starLevel + ",PVISAGE=" + pVisage
				+ ",TPOST='" + tPost + "',DEPT=" + dept + ",POST=" + post
				+ ",DUTIES=" + duties + ",CONTRACT=" + contract + ",GSCH='"
				+ gsch + "',EDUCATION=" + education + ",CCARD=" + cCard
				+ ",ELEVEL=" + eLevel + ",MLEVEL=" + mLevel + ",RP='" + rp
				+ "',TERM=" + term + ",SPECIALTY='" + specialty + "', FNAME='"
				+ fName + "',FAGE='" + fAge + "',FRELATION='" + fRelation
				+ "',FUNITS='" + fUnits + "',LEFTJOB=" + leftJob + ",ISFORMAL="
				+ isFormal + ",SPECIAL='" + special + "',INDATE='" + inDate
				+ "',GRADUATION='" + graduation + "',AGREEMENT='" + agreement
				+ "',ENDDATE='" + endDate + "',TEACHER='" + teacher
				+ "',INSURANCE=" + insurance + ",REMARKED='" + remarked + "'";
		
		if(!"".equals(photo)) {
			sql += ", PHOTO='" + photo + "'";
		}
		
		sql += " where EID=" + eid;
		
		DAO dao = new DAO();
		
		try {
			int num = dao.execCommand(ConstValue.DBNAME, sql);
			
			if (num > 0) {
				return "success";
			} else {
				return "failure";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 删除职工记录
	 * @param jso
	 * @return
	 */
	public String deleteEmployee(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		int eid = Integer.parseInt(jsobject.getString("eid"));
		
		// 删除原来的头像
		deleteOldImage(eid);
		
		String delEmp = "delete from hr_employee where EID ='" + eid + "'";
		
		DAO dao = new DAO();
		try {
			ArrayList<String> al = new ArrayList<String>();
			al.add(delEmp);
			
			boolean flag = dao.execCommand(ConstValue.DBNAME, al);
			
			if (flag) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 删除原来的头像
	 * @param eid
	 */
	@SuppressWarnings("unchecked")
	private void deleteOldImage(int eid) {
		String sql = "select PHOTO from hr_employee where eid=" + eid;

		DAO dao = new DAO();
		
		String oldPhotoPath = "";

		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				Hashtable<String, String> table = (Hashtable<String, String>) v.get(0);
				oldPhotoPath = table.get("PHOTO");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		oldPhotoPath = StringUtils.replace(oldPhotoPath, "$", "/");
		
		File oldPhoto = new File(oldPhotoPath);
		oldPhoto.delete();
	}
	
	/**
	 * 根据名称获取ID
	 * @param clName
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private int getClCodeByClName(String clName) {
		String sql = "select CL_CODE FROM cm_commlist where CL_NAME='" + clName + "'";
		
		DAO dao = new DAO();
		
		int clCode = -1;
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				Hashtable<String, String> table = (Hashtable<String, String>) v.get(0);
				clCode = Integer.parseInt(table.get("CL_CODE"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return clCode;
	}
}
